<?php

/**
 *
 * @author Administrator
 */
class DBAccess {
    // TODO - Insert your code here
    
    /**
     */
    function __construct() {
        
        // TODO - Insert your code here
    }

    /**
     * private function. Make conidtion string query from condition array.
     * @param array $conArray the array of contition.
     * @return string $conString the condition query string.
     */
    private function makeConditionQuery($conArray) {
        
        // in case array conditioin is null or has none element
        if ($conArray == null || count( $conArray) == 0) {
            return "";
        } else { // other whise
            $i = 0;
            $conString = " WHERE ";
            foreach ( $conArray as $key => $value ) {
                if ($i % 2 == 0) {
                    $conString .= " `" . $key . "` = '" . $value . "' ";
                } else {
                    $conString .= $value;
                }
                $i ++;
            }
            
            return $conString;
        }
    }

    /**
     * private function. make order query string from order array
     * @param array $orderArray the order array
     * @return string $orderString the order query string
     */
    private function makeOrderQuery($orderArray) {
        
        // in case array conditioin is null or has none element
        if ($orderArray == null || count( $orderArray) == 0) {
            return "";
        } else { // other whise
            $orderString = " ORDER BY";
            foreach ( $orderArray as $key => $value ) {
                $orderString .= " `" . $key . "` " . $value . ",";
            }
            
            return substr( $orderString, 0, strlen( $orderString) - 1);
        }
    }

    /**
     * private function make limit query string form page number and limit per page contanst
     * @param int $pageNumber the current page number want to show * @param int $limit the limit per page
     * @return string limit query string
     */
    private function makeLimitQuery($pageNumber, $limit) {

        $start = ($pageNumber - 1) * $limit;
        return " LIMIT " . $start . " , " . $limit;
    }

    /**
     * private function. Make conidtion string query from condition array.
     * @param array $conArray the array of contition.
     * @return string $conString the condition query string.
     */
    private function makeDifficultCondition($conArray) {
        
        // in case array conditioin is null or has none element
        if ($conArray == null || count( $conArray) == 0) {
            return "";
        }
        
        $conString = " WHERE ";
        
        // create string query
        $i = 0;
        foreach ( $conArray as $condidtion ) {
            if ($i % 2 != 0) {
                $conString .= $condidtion;
            } else {
                // echo $i . "-> key => " . $condidtion['key'];
                switch ($condidtion['key']) {
                    case EQUAL :
                        $conString .= " `" . $condidtion['column'] . "` " . EQUAL . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case NOT_EQUAL :
                        $conString .= " `" . $condidtion['column'] . "` " . NOT_EQUAL . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case LESS :
                        $conString .= " `" . $condidtion['column'] . "` " . EQUAL . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case GREAT :
                        $conString .= " `" . $condidtion['column'] . "` " . LESS . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case LESS_EQUAL :
                        $conString .= " `" . $condidtion['column'] . "` " . LESS_EQUAL . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case GREAT_EQUAL :
                        $conString .= " `" . $condidtion['column'] . "` " . GREAT_EQUAL . " '" . $condidtion['value'] . "' ";
                        break;
                    
                    case LIKE :
                        $conString .= " `" . $condidtion['column'] . "` " . LIKE . " ('%" . $condidtion['value'] . "%') ";
                        break;
                    
                    case NOT_LIKE :
                        $conString .= " `" . $condidtion['column'] . "` " . NOT_LIKE . " ('%" . $condidtion['value'] . "%') ";
                        break;
                    
                    case IN :
                        $inValue = "";
                        foreach ( $condidtion['value'] as $value ) {
                            $inValue .= " '" . $value . "', ";
                        }
                        $inValue = substr( $inValue, 0, strlen( $inValue) - 2);
                        $conString .= " `" . $condidtion['column'] . "` " . IN . " ($inValue) ";
                        break;
                    
                    case NOT_IN :
                        $inValue = "";
                        foreach ( $condidtion['value'] as $value ) {
                            $inValue .= " '" . $value . "', ";
                        }
                        $inValue = substr( $inValue, 0, strlen( $inValue) - 2);
                        
                        $conString .= " `" . $condidtion['column'] . "` " . NOT_IN . " ($inValue) ";
                        break;
                    
                    case IS_NULL :
                        $conString .= " `" . $condidtion['column'] . "` " . IS_NULL;
                        break;
                    
                    case NOT_NULL :
                        $conString .= " `" . $condidtion['column'] . "` " . NOT_NULL;
                        break;
                    
                    case BETWEEN :
                        $conString .= " `" . $condidtion['column'] . "` " . BETWEEN . " '" . $condidtion['value'][0] . "' AND '" . $condidtion['value'][1] . "' ";
                        break;
                }
            }
            $i ++;
        }
        
        return $conString;
    }

    /**
     * private fuction make select query string
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @param int $pageNumber the current page number
     * @param int $limit the limit number per page
     * @return string $queryString the query string
     */
    private function makeDifficultSelectQuery($columArray, $tableName, $conArray = null, $orderArray = null, $pageNumber = null, $limit = null, $distint) {
        
        // check column array
        if ($columArray == null || count( $columArray) == 0) {
            return null;
        }
        
        // check table name
        if ($tableName == null || trim( $tableName) == "") {
            return null;
        }
        
        if ($distint){
        	// create select statement
        	$queryString = " SELECT DISTINCT ";
        } else {
        	// create select statement
        	$queryString = " SELECT ";
        }
        
        
        // plus column name
        foreach ( $columArray as $key => $value ) {
            $queryString .= "`" . $value . "`, ";
        }
        $queryString = substr( $queryString, 0, strlen( $queryString) - 2);
        
        // plus table name
        $queryString .= " FROM `" . $tableName . "` ";
        
        // plus condition
        if ($conArray != null) {
            $queryString .= $this->makeDifficultCondition( $conArray);
        }
        
        // plus order
        if ($orderArray != null) {
            $queryString .= $this->makeOrderQuery( $orderArray);
        }
        
        // plus limit
        if ($pageNumber != null && $limit != null) {
            $queryString .= $this->makeLimitQuery( $pageNumber, $limit);
        }
        
        return $queryString;
    }

    /**
     * private fuction make select query string
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @param int $pageNumber the current page number
     * @param int $limit the limit number per page
     * @param int $distint the limit number per page
     * @return string $queryString the query string
     */
    private function makeSelectQuery($columArray, $tableName, $conArray = null, $orderArray = null, $pageNumber = null, $limit = null, $distint = false) {
        
        // check column array
        if ($columArray == null || count( $columArray) == 0) {
            return null;
        }
        
        // check table name
        if ($tableName == null || trim( $tableName) == "") {
            return null;
        }
        
        // create select statement
        if ($distint) {
        	$queryString = " SELECT DISTINCT ";
        } else {
        	$queryString = " SELECT ";
        }
        
        // plus column name
        foreach ( $columArray as $key => $value ) {
            $queryString .= "`" . $value . "`, ";
        }
        $queryString = substr( $queryString, 0, strlen( $queryString) - 2);
        
        // plus table name
        $queryString .= " FROM `" . $tableName . "` ";
        
        // plus condition
        if ($conArray != null) {
            $queryString .= $this->makeConditionQuery( $conArray);
        }
        
        // plus order
        if ($orderArray != null) {
            $queryString .= $this->makeOrderQuery( $orderArray);
        }
        
        // plus limit
        if ($pageNumber != null && $limit != null) {
            $queryString .= $this->makeLimitQuery( $pageNumber, $limit);
        }
        
        return $queryString;
    }

    /**
     * change object to array
     * @param $object object want to chagne
     * @return $array array
     */
    private function toArray($object) {

        $array = array();
        foreach ( $object as $member => $data ) {
            $array[$member] = $data;
        }
        return $array;
    }

    /**
     * Select one record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @return string $rowArray a PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectOneByCause($columArray, $tableName, $conArray, $orderArray = null) {
        
        // call private method makeQueryString to create queryString.
        $queryString = $this->makeSelectQuery( $columArray, $tableName, $conArray, $orderArray);
        
        //echo($queryString); 
        
        // check queryString.
        if ($queryString == null) {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        if ($row = mysql_fetch_object( $result)) {
            return $this->toArray( $row);
        } else {
            return null;
        }
    
    }

    /**
     * Select all record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $orderArray the array order
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectAll($columArray, $tableName, $orderArray = null) {
        
        // call private method makeQueryString to create queryString.
        $queryString = $this->makeSelectQuery( $columArray, $tableName, null, $orderArray);
        
        // check queryString.
        if ($queryString == null) {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        $arrayObject = array();
        $n = 0;
        while ( $row = mysql_fetch_object( $result) ) {
            $arrayObject[$n] = $this->toArray( $row);
            $n ++;
        }
        
        return $arrayObject;
    }

    /**
     * Select one record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectManyByCause($columArray, $tableName, $conArray, $orderArray = null) {
        
        // call private method makeQueryString to create queryString.
        $queryString = $this->makeSelectQuery( $columArray, $tableName, $conArray, $orderArray);
        
        // check queryString.
        if ($queryString == null) {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        $arrayObject = array();
        $n = 0;
        while ( $row = mysql_fetch_object( $result) ) {
            $arrayObject[$n] = $this->toArray( $row);
            $n ++;
        }
        
        return $arrayObject;
    
    }
    
    /**
     * Select one record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectDistintByCause($columArray, $tableName, $conArray, $orderArray = null) {
    
    	// call private method makeQueryString to create queryString.
    	$queryString = $this->makeSelectQuery( $columArray, $tableName, $conArray, $orderArray, null, null, true);
    	
    	echo $queryString;
    
    	// check queryString.
    	if ($queryString == null) {
    		return null;
    	}
    
    	// create statement and execute select query string.
    	$stm = new Statement();
    	$result = $stm->executeQuerySelect( $queryString);
    
    	// fetch object and return
    	$arrayObject = array();
    	$n = 0;
    	while ( $row = mysql_fetch_object( $result) ) {
    		$arrayObject[$n] = $this->toArray( $row);
    		$n ++;
    	}
    
    	return $arrayObject;
    
    }

    /**
     * Select one record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @param int $pageNumber the current page number
     * @param int $limit the limit number per page
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectManyByCauseWithPaging($columArray, $tableName, $conArray, $orderArray, $pageNumber, $limit) {
        
        // call private method makeQueryString to create queryString.
        $queryString = $this->makeSelectQuery( $columArray, $tableName, $conArray, $orderArray, $pageNumber, $limit);
        
        //echo $queryString;
        
        // check queryString.
        if ($queryString == null) {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        $arrayObject = array();
        $n = 0;
        while ( $row = mysql_fetch_object( $result) ) {
            $arrayObject[$n] = $this->toArray( $row);
            $n ++;
        }
        
        return $arrayObject;
    
    }

    /**
     * Select one record from a table in database with some condition
     * @param array $columArray the array colum name want to select
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     * @param array $orderArray the array order
     * @param int $pageNumber the current page number
     * @param int $limit the limit number per page
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectDifficultRecord($columArray, $tableName, $conArray = null, $orderArray = null, $pageNumber = null, $limit = null, $distint = false) {
        
        // call private method makeQueryString to create queryString.
        $queryString = $this->makeDifficultSelectQuery( $columArray, $tableName, $conArray, $orderArray, $pageNumber, $limit, $distint);
        
        //echo $queryString . "<br />";
        
        // check queryString.
        if ($queryString == null) {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        $arrayObject = array();
        $n = 0;
        while ( $row = mysql_fetch_object( $result) ) {
            $arrayObject[$n] = $this->toArray( $row);
            $n ++;
        }
        
        return $arrayObject;
    
    }

    /**
     * count the record in table
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     */
    public function selectCountRecord($tableName, $conArray = null) {
        
        // create query count
        $queryString = "SELECT COUNT(ID) AS COUNT FROM `" . $tableName . "`";
        
        //echo $queryString;
        
        // plus conidtion
        if ($conArray != null) {
            $queryString .= $this->makeConditionQuery( $conArray);
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        if ($row = mysql_fetch_object( $result)) {
            $count = ( int ) $row->COUNT;
            return ($count);
        }
        
        return 0;
    }
    
    /**
     * count the record in table
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     */
    public function selectCountRecordDifficult($tableName, $conArray = null) {
    
    	// create query count
    	$queryString = "SELECT COUNT(ID) AS COUNT FROM `" . $tableName . "`";
    
    	//echo $queryString;
    	
    	// plus conidtion
    	if ($conArray != null) {
    		$queryString .= $this->makeDifficultCondition($conArray);
    	}
    
    	// create statement and execute select query string.
    	$stm = new Statement();
    	$result = $stm->executeQuerySelect( $queryString);
    
    	// fetch object and return
    	if ($row = mysql_fetch_object( $result)) {
    		$count = ( int ) $row->COUNT;
    		return ($count);
    	}
    
    	return 0;
    }

    /**
     * count the record in table
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     */
    public function selectMaxRecord($column, $tableName, $conArray = null) {
        
        // create query count
        $queryString = "SELECT MAX(`$column`) AS MAXRECORD FROM `$tableName`";
        
        // plus conidtion
        if ($conArray != null) {
            $queryString .= $this->makeConditionQuery( $conArray);
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        if ($row = mysql_fetch_object( $result)) {
            $max = ( int ) $row->MAXRECORD;
            return ($max);
        }
        
        return 0;
    }

    /**
     * count the record in table
     * @param string $tableName the table name want to select form
     * @param array $conArray the array condition
     */
    public function selectCountDifficultRecord($tableName, $conArray = null) {
        
        // create query count
        $queryString = "SELECT COUNT(ID) AS COUNT FROM `" . $tableName . "`";
        
        // plus conidtion
        if ($conArray != null) {
            $queryString .= $this->makeDifficultCondition( $conArray); // makeConditionQuery( $conArray);
        }
        
        // echo $queryString;
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $queryString);
        
        // fetch object and return
        if ($row = mysql_fetch_object( $result)) {
            $count = ( int ) $row->COUNT;
            return ($count);
        }
        
        return 0;
    }
    
    
    /**
     * Select join two table
     * @param unknown_type $columArray1
     * @param unknown_type $columArray2
     * @param unknown_type $tableName1
     * @param unknown_type $tableName2
     * @param unknown_type $joinCause
     * @param unknown_type $conArray
     * @param unknown_type $orderArray
     * @param unknown_type $pageNumber
     * @param unknown_type $limit
     * @return multitype:
     */
    public function selectJoinTwoTable( $columArray1, $columArray2, $tableName1, $tableName2, $joinCause, $conArray, $orderArray, $pageNumber, $limit){
        
        $result = array();
        
        return $result;
    }

    /**
     * Select one record from a table in database with some condition
     * @param $sqlQuery the query String
     * @return a array PHP Object (DTO) tranffer infomation select form databse (In case have exception return null)
     */
    public function selectBySQLQuery($sqlQuery) {
        
        // check queryString.
        if ($sqlQuery == null || trim( $sqlQuery) == "") {
            return null;
        }
        
        // create statement and execute select query string.
        $stm = new Statement();
        $result = $stm->executeQuerySelect( $sqlQuery);
        
        // fetch object and return
        $arrayObject = array();
        $n = 0;
        while ( $row = mysql_fetch_object( $result) ) {
            $arrayObject[$n] = $this->toArray( $row);
            $n ++;
        }
        
        return $arrayObject;
    
    }

    /**
     * Create a insert query string to execute
     * @param $data data to insert
     * @param $table the table name want to select
     * @param $condition the condition of queryString to execute
     * @return query string (In case have exception return flase)
     */
    private function makeInsertQueryString($data, $table) {
        
        // check colum select. In case colum array have none element, return flase.
        if ($data == null || count( $data) == 0) {
            return false;
        }
        
        // check table name. In case table name is incorrect, return false.
        if ($table == null || trim( $table) == "") {
            return false;
        }
        
        // create data to insert
        $columName = "";
        $dataInsert = "";
        foreach ( $data as $key => $value ) {
            $value = str_replace("'", "''", $value);
        	$value = str_replace("\''", "''", $value);
            $columName .= "`" . $key . "`, ";
            $dataInsert .= "'" . $value  . "', ";
        }
        $columName = substr( $columName, 0, - 2);
        $dataInsert = substr( $dataInsert, 0, - 2);
        
        $queryString = "INSERT INTO `" . $table . "` (" . $columName . ") VALUES (" . $dataInsert . ")";
        
        return $queryString;
    }

    /**
     * Create a update query string to execute
     * @param $data data to insert
     * @param $table the table name want to select
     * @return query string (In case have exception return flase)
     */
    private function makeUpdateQueryString($data, $table) {
        
        // check colum select. In case colum array have none element, return flase.
        if ($data == null || count( $data) == 0) {
            return false;
        }
        
        // check table name. In case table name is incorrect, return false.
        if ($table == null || trim( $table) == "") {
            return false;
        }
        
        // create data to insert
        $dataUpdate = "";
        foreach ( $data as $key => $value ) {
            $value = str_replace("'", "''", $value);
        	$value = str_replace("\''", "''", $value);
            $dataUpdate .= "`" . $key . "` = '" . $value . "', ";
        }
        $dataUpdate = substr( $dataUpdate, 0, - 2);
        
        $queryString = "UPDATE `" . $table . "` SET " . $dataUpdate;
        
        $conditionQuery = "WHERE `ID` = " . $data['ID'];
        
        $queryString .= $conditionQuery;
        
        return $queryString;
    }

    /**
     * update data into database
     * @param $data array data with key is colum and value is values
     * @param $table is table name
     * @param $condition is array condition
     * @return true or false
     */
    public function updateData($data, $table) {

        $stm = new Statement();
        $queryString = $this->makeUpdateQueryString( $data, $table);
        $execute = $stm->executeQueryUpdate( $queryString);
        return $execute;
    }

    /**
     * insert data into database
     * @param $data array data with key is colum and value is values
     * @param $table is table name
     * @return true or false
     */
    public function insertData($data, $table) {

        $stm = new Statement();
        $queryString = $this->makeInsertQueryString( $data, $table);
        $execute = $stm->executeQueryUpdate( $queryString);
        return $execute;
    }

    /**
     * delete on row
     * @param unknown_type $table
     * @param unknown_type $id
     */
    public function deleteData($table, $id) {

        $stm = new Statement();
        $queryString = "DELETE FROM `$table` WHERE `ID` = $id";
        $execute = $stm->executeQueryUpdate( $queryString);
        return $execute;
    }

    /**
     */
    function __destruct() {
        
        // TODO - Insert your code here
    }
}
?>